package cn.edu.hunau.dao.impl;

import cn.edu.hunau.dao.BackstageDao;
import cn.edu.hunau.exception.DaoException;
import cn.edu.hunau.pojo.User;
import cn.edu.hunau.utils.DBUtils;

/**
 * @Author 苏佳
 * @Date 2023 01 03 21 31
 **/
public class BackstageDaoImpl implements BackstageDao {
    @Override
    public int countOrderByMouthAndOstate(int month,int ostate) throws DaoException {
        String sql="select count(*) FROM t_order where DATE_FORMAT(dotime,'%c')=? and ostate=?";
        return DBUtils.query(sql,rs -> {
            int count = -1;
            if(rs.next()){
                count = rs.getInt(1);
            }
            return count;
        },month,ostate);
    }

    @Override
    public int countByMonth(int month) throws DaoException {
        String sql="select count(id) from t_user where DATE_FORMAT(create_at,'%c')=?";
        return DBUtils.query(sql,rs -> {
            int count = -1;
            if(rs.next()){
                count = rs.getInt(1);
            }
            return count;
        },month);
    }

    @Override
    public int queryCategorySumByCid(int cid) throws DaoException {
        String sql="select t_orderitem.pid,t_product.cid,t_orderitem.qty from t_product,t_orderitem where t_orderitem.pid=t_product.id";
        return DBUtils.query(sql,rs -> {
            int count=-1;
            int cidQty=0;
            while(rs.next()){
                count=rs.getInt("cid");
                if(count==cid){
                    cidQty+=rs.getInt("qty");
                }
            }
            return cidQty;
        });
    }

    @Override
    public User queryByUserNameAndPwd(String username, String pwd) throws DaoException {
        String sql="select id,username from t_superuser where username=? and pwd=md5(?)";

        return DBUtils.query(sql,(rs)->{
            User u=null;
            if(rs.next()){
                u=new User();
                u.setId(rs.getInt("id"));
                u.setUsername(rs.getString("username"));
            }
            return u;
        },username,pwd);
    }
}
